package com.xlserver.service;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.UpdateWrapper;
import com.xlserver.dao.*;

import com.xlserver.domain.*;
import com.xlserver.util.TimeUtil;
import com.xlserver.util.model.CounselorInfo;
import com.xlserver.util.model.SupervisorInfo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.sql.Date;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;


@Service
public class AdministratorService {
    @Autowired
    CounselorMapper counselorMapper;
    @Autowired
    SupervisorBandCounselorMapper supervisorBandCounselorMapper;
    @Autowired
    ConsultationMapper consultationMapper;
    @Autowired
    SupervisorMapper supervisorMapper;
    @Autowired
    TimeUtil timeUtil;
    @Autowired
    LoginInfoMapper loginInfoMapper;
    @Autowired
    PhotoMapper photoMapper;
    @Autowired
    MessageMapper messageMapper;
    @Autowired
    UserMapper userMapper;
    @Autowired
    StateMapper stateMapper;
    @Autowired
    AdministratorMapper administratorMapper;



    /**
     * 获取所有咨询师
     */
    public JSONArray getAllCounselors(){
        return parseCounselors(counselorMapper.selectList(null));
    }

    /**
     通过姓名模糊查找咨询师
     */
    public JSONArray getCounselorByName(String name){
        List<Counselor> counselors = counselorMapper.selectList(
                new QueryWrapper<Counselor>().eq("cName",name));
        if (counselors.size() == 0){
            return new JSONArray();
        }
        return parseCounselors(counselors);
    }

    /**
     *新增咨询师
     */
    public JSONObject insertCounselor(CounselorInfo counselorInfo){
        JSONObject res = new JSONObject();
        //idNumber唯一
        if (counselorMapper.selectOne(new QueryWrapper<Counselor>().eq("cIdNumber",counselorInfo.getIdNumber())) != null){
            res.put("result","failure");
        }
        else {
            Counselor counselor = new Counselor(counselorInfo);
//            String username = getRandomUsername(6);
//            while (counselorMapper.selectOne(new QueryWrapper<Counselor>().eq("cUsername",username)) != null){
//                username = getRandomUsername(6);
//            }
            String username = counselorInfo.getUsername();
            if (counselorMapper.selectOne(new QueryWrapper<Counselor>().eq("cUsername",username)) != null){
                res.put("result","failure");
                return res;
            }
            counselor.setUsername(username);
            counselor.setCid(setCounselorId());//设置id
            counselorMapper.insert(counselor);

            counselor = counselorMapper.selectOne(new QueryWrapper<Counselor>().eq("cUsername",username));
            System.out.println(counselor+"====");
            int counselorId = counselor.getCid();
            loginInfoMapper.insert(new LoginInfo(counselorId, counselorInfo.getPassword())
            );
            counselorInfo.getBandSupervisors().forEach(supervisor->{
                int supervisorId = supervisor.getSupervisor();
                supervisorBandCounselorMapper.insert(new SupervisorBandCounselor(supervisorId,counselorId));
            });
            //插入在线状态
            stateMapper.insert(new State(counselorId,-1));
            res.put("result","success");
        }
        return res;
    }
    /**
     *设置咨询师id，2 开头，五位数
     */
    private int setCounselorId(){
        String id = "2"+getRandom(4);
        while (userMapper.selectById(id)!=null){
            id="2"+getRandom(4);
        }
        return Integer.parseInt(id);
    }
    /**
     * 修改咨询师信息
     */
    public JSONObject updateCounselor(CounselorInfo counselorInfo){
        //基本信息
        counselorMapper.update(null, new UpdateWrapper<Counselor>()
                        .eq("cid",counselorInfo.getId())
                        .set("cName",counselorInfo.getName())
                        .set("cGender",counselorInfo.getGender())
                        .set("cAge",counselorInfo.getAge())
                        .set("cPhoneNum",counselorInfo.getPhoneNum())
                        .set("cEmail",counselorInfo.getEmail())
                        .set("cJob",counselorInfo.getJob())
                        .set("cTitle",counselorInfo.getTitle())
                        .set("cSchedule",counselorInfo.getSchedule())
        );
        //密码
        loginInfoMapper.update(
                new LoginInfo(counselorInfo.getId(),counselorInfo.getPassword()),
                new UpdateWrapper<LoginInfo>().eq("id",counselorInfo.getId()));
        //绑定督导
        QueryWrapper<SupervisorBandCounselor> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("cid",counselorInfo.getId());
        supervisorBandCounselorMapper.delete(queryWrapper);
        counselorInfo.getBandSupervisors()
                .forEach(supervisor -> supervisorBandCounselorMapper.insert(new SupervisorBandCounselor(supervisor.getSupervisor(),counselorInfo.getId())));
        JSONObject res = new JSONObject();
        res.put("result","success");
        return res;
    }
    /**
     * 查询某个咨询师的相关信息
     */
    public JSONObject getCounselor(int id){
        Counselor counselor = counselorMapper.selectById(id);
        if (counselor == null){
            JSONObject res = new JSONObject();
            res.put("result","failure");
            return res;
        }
        JSONObject res = new JSONObject();
        res.put("id",counselor.getCid());
        res.put("name",counselor.getName());
        res.put("gender",counselor.getGender());
        res.put("age",counselor.getAge());
        res.put("phoneNum",counselor.getPhoneNum());
        res.put("email",counselor.getEmail());
        res.put("job",counselor.getJob());
        res.put("title",counselor.getTitle());
        res.put("idNumber",counselor.getIdNumber());
        res.put("username",counselor.getUsername());
        //密码
        res.put("password",loginInfoMapper.selectById(id).getPassword());
        //绑定督导（id）
        JSONArray supervisors = new JSONArray();
        supervisorBandCounselorMapper.selectList(new QueryWrapper<SupervisorBandCounselor>().eq("cid",id)).forEach(supervisorBandCounselor -> {
            JSONObject supervisor = new JSONObject();
            int sid = supervisorBandCounselor.getSid();
            supervisor.put("id",sid);
            supervisor.put("name",supervisorMapper.selectById(sid).getName());
            supervisors.add(supervisor);
        });
        res.put("bandSupervisors",supervisors);
        res.put("schedule",counselor.getSchedule());
        res.put("result","success");
        return res;
    }
    /**
     * 获取所有督导
     */
    public JSONArray getAllSupervisors(){
        List<Supervisor> supervisors = supervisorMapper.selectList(null);
        if (supervisors.size() == 0){
            return new JSONArray();
        }
        return parseSupervisors(supervisors);
    }
    /**
     * 使用name模糊查询督导
     */
    public JSONArray getSupervisorByName(String name){
        List<Supervisor> supervisors = supervisorMapper.selectList(new QueryWrapper<Supervisor>().eq("sName",name));
        if (supervisors.size() == 0){
            return new JSONArray();
        }
        return parseSupervisors(supervisors);
    }
    /**
     * 根据id查看督导详细信息
     */
    public JSONObject detailsOfSupervisor(int id){
        JSONObject ret = (JSONObject) JSONObject.toJSON(
                supervisorMapper.selectById(id));
        List<SupervisorBandCounselor> bands = supervisorBandCounselorMapper.selectList(
                        new QueryWrapper<SupervisorBandCounselor>().eq("sid",id)
                );
        //绑定的咨询师
        JSONArray counselors = new JSONArray();
        bands.stream().map(SupervisorBandCounselor::getCid).collect(Collectors.toList()).forEach(cid->{
            JSONObject counselor = new JSONObject();
            counselor.put("id",cid);
            counselor.put("name",counselorMapper.selectById(cid).getName());
            counselors.add(counselor);
        });
        ret.put("bandCounselors",counselors);
        ret.put("password",loginInfoMapper.selectById(id).getPassword());
        return ret;

    }
    /**
     * 新增督导
     * 涉及supervisor_band_counselor，supervisor_info，login_info
     */
    public JSONObject addSupervisor(SupervisorInfo supervisorInfo){
        JSONObject res = new JSONObject();
        Supervisor supervisor = new Supervisor(supervisorInfo);
        //username，idNumber，certificationId查重
        if (supervisorMapper.selectOne(new QueryWrapper<Supervisor>().eq("sUsername",supervisor.getUsername())) != null
        || supervisorMapper.selectOne(new QueryWrapper<Supervisor>().eq("sIdNumber",supervisor.getIdNumber())) != null
        || supervisorMapper.selectOne(new QueryWrapper<Supervisor>().eq("sCertificationId",supervisor.getCertificationId())) != null){
            res.put("result","failure");
            return res;
        }
        supervisor.setSid(setSupervisorId());//设置id
        supervisorMapper.insert(supervisor);
        supervisor = supervisorMapper.selectOne(new QueryWrapper<Supervisor>().eq("sIdNumber",supervisorInfo.getIdNumber()));
        //添加督导密码
        loginInfoMapper.insert(new LoginInfo(supervisor.getSid(),supervisorInfo.getPassword()));
        //绑定咨询师，这里保证咨询师一定存在？
        int supervisorId = supervisor.getSid();
        if(!(supervisorInfo.getBandCounselors() == null) && !supervisorInfo.getBandCounselors().isEmpty()){
            supervisorInfo.getBandCounselors().forEach(
                    counselor -> supervisorBandCounselorMapper.insert(new SupervisorBandCounselor(supervisorId,counselor.getCounselor())));
        }
        //插入在线状态
        stateMapper.insert(new State(supervisorId,-1));
        res.put("result","success");
        return res;
    }
    /**
     *设置督导id，3 开头，五位数
     */
    private int setSupervisorId(){
        String id = "3"+getRandom(4);
        while (counselorMapper.selectById(id)!=null){
            id="3"+getRandom(4);
        }
        return Integer.parseInt(id);
    }
    private String getRandom(int len) {
        Random r = new Random();
        StringBuilder rs = new StringBuilder();
        for (int i = 0; i < len; i++) {
            rs.append(r.nextInt(10));
        }
        return rs.toString();
    }

    /**
     * 修改督导信息
     */
//    督导id 姓名 性别 年龄 身份证号码 电话 邮箱 绑定的咨询师（id） 密码 工作单位 职称 督导资质 资质编号 周值班安排（1111000）
    public JSONObject updateSupervisor(SupervisorInfo supervisorInfo){
        JSONObject ret = new JSONObject();
        //基本信息，对身份证号码与资质编号判空
        if(supervisorMapper.selectOne(new QueryWrapper<Supervisor>().eq("sCertificationId",supervisorInfo.getCertificationId())
                .ne("sid",supervisorInfo.getId())) != null
        || supervisorMapper.selectOne(new QueryWrapper<Supervisor>().eq("sIdNumber",supervisorInfo.getIdNumber())
                .ne("sid",supervisorInfo.getId())) != null){
            ret.put("result","failure");
            return ret;
        }
        supervisorMapper.update(null,new UpdateWrapper<Supervisor>()
                .eq("sid",supervisorInfo.getId())
                .set("sName",supervisorInfo.getName())
                .set("sGender",supervisorInfo.getGender())
                .set("sAge",supervisorInfo.getAge())
                .set("sIdNumber",supervisorInfo.getIdNumber())
                .set("sPhoneNum",supervisorInfo.getPhoneNum())
                .set("sJob",supervisorInfo.getJob())
                .set("sEmail",supervisorInfo.getEmail())
                .set("sTitle",supervisorInfo.getTitle())
                .set("sCertification",supervisorInfo.getCertification())
                .set("sCertificationId",supervisorInfo.getCertificationId())
                .set("sSchedule",supervisorInfo.getSchedule())
        );
        //密码
        loginInfoMapper.update(null,new UpdateWrapper<LoginInfo>()
                .eq("id",supervisorInfo.getId())
                .set("password",supervisorInfo.getPassword()));
        //绑定咨询师
        supervisorBandCounselorMapper.delete(new QueryWrapper<SupervisorBandCounselor>().eq("sid",supervisorInfo.getId()));
        supervisorInfo.getBandCounselors().forEach(counselor -> {
            supervisorBandCounselorMapper.insert(new SupervisorBandCounselor(supervisorInfo.getId(),counselor.getCounselor()));
        });

        ret.put("result","success");
        return ret;
    }
    /**
     * 删除咨询师
     */
    //是否删除相关聊天记录
    public JSONObject deleteCounselor(int id){
        JSONObject ret = new JSONObject();
        //删除counselor
        counselorMapper.delete(new QueryWrapper<Counselor>().eq("cid",id));
        //删除绑定信息
        supervisorBandCounselorMapper.delete(new QueryWrapper<SupervisorBandCounselor>().eq("cid",id));
        //删除登录信息
        loginInfoMapper.delete(new QueryWrapper<LoginInfo>().eq("id",id));
        //删除对话信息
        deleteConsultation("cid",id);
        deleteConsultation("uid",id);
        //删除在线状态
        stateMapper.deleteById(id);
        ret.put("result","success");
        return ret;
    }
    /**
     * 删除督导
     */
    public JSONObject deleteSupervisor(int id){
        JSONObject ret = new JSONObject();
        //删除supervisor
        supervisorMapper.delete(new QueryWrapper<Supervisor>().eq("sid",id));
        //删除绑定信息
        supervisorBandCounselorMapper.delete(new QueryWrapper<SupervisorBandCounselor>().eq("sid",id));
        //删除登录信息
        loginInfoMapper.delete(new QueryWrapper<LoginInfo>().eq("id",id));
        //删除对话信息
        deleteConsultation("cid",id);
        //删除在线状态
        stateMapper.deleteById(id);
        ret.put("result","success");
        return ret;
    }
    /**
     * 获取所有访客
     */
    public JSONArray getAllUsers(){
        List<User> users = userMapper.selectList(null);
        //总条数
        JSONObject num = new JSONObject();
        num.put("sum",users.size());
        //用户信息
        JSONArray res = parseUsers(users);
        res.add(num);
        return res;
    }
    /**
     * 根据name查找user
     */
    public JSONArray getUserByName(String name){
        List<User> users = userMapper.selectList(new QueryWrapper<User>()
                .eq("uName",name));
        System.out.println("name:"+name);
        System.out.println(users.toString());
        return parseUsers(users);
    }
    /**
     * 禁用访客
     */
    public JSONObject banUser(int id){
        userMapper.update(null,new UpdateWrapper<User>()
                .eq("uid",id)
                .set("uForbidden",1));
        JSONObject ret = new JSONObject();
        ret.put("result","success");
        return ret;
    }
    /**
     * 解禁访客
     */
    public JSONObject unlockUser(int id){
        userMapper.update(null,new UpdateWrapper<User>()
                .eq("uid",id)
                .set("uForbidden",0));
        JSONObject ret = new JSONObject();
        ret.put("result","success");
        return ret;
    }

    /**
     *将list的users解析为jsonarray
     */
    private JSONArray parseUsers(List<User> users){
        JSONArray res = new JSONArray();
        users.forEach(user -> {
            JSONObject ret = new JSONObject();
            ret.put("id",user.getUserId());
            ret.put("name",user.getUsername());
            ret.put("gender",user.getGender());
            ret.put("phoneNum",user.getPhoneNumber());
            ret.put("emergencyContactName",user.getEmergencyContactName());
            ret.put("emergencyPhoneNumber",user.getEmergencyPhoneNumber());
            ret.put("isForbidden",user.getIsForbidden());
            ret.put("accessDate",user.getAccessDate());
            res.add(ret);
        });
        return res;
    }
    /**
     * 删除会话
     * 返回false表示无任何会话
     */
    private void deleteConsultation(String column, int id){
        List<Consultation> consultations = consultationMapper.selectList(new QueryWrapper<Consultation>().eq(column,id));
        if (consultations.size() == 0){
            return;
        }
        consultations.forEach(consultation -> {
            int hid = consultation.getHid();
            photoMapper.delete(new QueryWrapper<Photo>().eq("hid",hid));
            messageMapper.delete(new QueryWrapper<Message>().eq("hid",hid));
        });
        consultationMapper.delete(new QueryWrapper<Consultation>().eq(column,id));
    }
    /**
     * 将list的supervisors及其相关信息解析为json格式
     */
    private JSONArray parseSupervisors(List<Supervisor> supervisors){
        JSONArray res = new JSONArray();
        supervisors.forEach(supervisor -> {
            JSONObject ret = new JSONObject();
            ret.put("id",supervisor.getSid());
            ret.put("name",supervisor.getName());
            //绑定的咨询师的username
            List<String> counselors = new ArrayList<>();
            supervisorBandCounselorMapper.selectList(
                            new QueryWrapper<SupervisorBandCounselor>().eq("sid",supervisor.getSid())).
                    forEach(supervisorBandCounselor -> {
                                counselors.add(counselorMapper.selectById(supervisorBandCounselor.getCid()).getUsername());
                            });
            ret.put("bandCounselors",JSONArray.parseArray(JSON.toJSONString(counselors)));
            //咨询总数
            ret.put("sumOfQuests",consultationMapper.selectList(
                            new QueryWrapper<Consultation>().eq("cid", supervisor.getSid())).
                    stream().filter(consultation -> !consultation.getBeginTime().
                            equals(consultation.getEndTime())).count());
            //咨询总时长
            try {
                ret.put("time",timeUtil.getTimeDiff(new HashSet<>(consultationMapper.selectList(
                        new QueryWrapper<Consultation>().eq("cid",supervisor.getSid())))));
            } catch (ParseException e) {
                e.printStackTrace();
            }
            ret.put("schedule",supervisor.getSchedule());
            res.add(ret);
        });
        return res;
    }
    /**
     * 将list中的咨询师解析为JSON格式
     */
    private JSONArray parseCounselors(List<Counselor> counselors){
        JSONArray ret = new JSONArray();
        counselors.forEach(counselor -> {
            JSONObject temp = new JSONObject();
            temp.put("id",counselor.getCid());
            temp.put("name",counselor.getName());
            temp.put("username",counselor.getUsername());

            JSONArray bandSupervisors = new JSONArray();
            supervisorBandCounselorMapper.selectList(
                            new QueryWrapper<SupervisorBandCounselor>().eq("cid",counselor.getCid())).
                    forEach(supervisorBandCounselor -> {
                        JSONObject supervisor = new JSONObject();
                        supervisor.put("sid",supervisorBandCounselor.getSid());
                        supervisor.put("sName",supervisorMapper.selectById(supervisorBandCounselor.getSid()).getName());
                        bandSupervisors.add(supervisor);
                    });
            temp.put("bandSupervisors",bandSupervisors);
            temp.put("questsCompleted",counselor.getQuestsCompleted());
            List<Consultation> consultations = consultationMapper.selectList(
                    new QueryWrapper<Consultation>().eq("cid",counselor.getCid()));
            try {
                temp.put("time",timeUtil.getTimeDiff(new HashSet<>(consultations)));
            } catch (ParseException e) {
                e.printStackTrace();
            }
            temp.put("averageRank",consultations.stream().mapToInt(Consultation::getCrank).average().orElse(0));
            temp.put("schedule",counselor.getSchedule());
            ret.add(temp);
        });
        return ret;
    }

    /**
     *生成随机username
     */
    public static String getRandomUsername(int length){
        String str="abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";
        Random random=new Random();
        StringBuilder sb=new StringBuilder();
        for(int i=0;i<length;i++){
            int number=random.nextInt(62);
            sb.append(str.charAt(number));
        }
        return sb.toString();
    }
    /**
     * 获取所有会话==
     */
    public JSONArray getAllChats(){
        return parseUsersToJSONArray(consultationMapper.selectList(null));
    }
    /**
     * 根据姓名获取会话==
     */
    public JSONArray getChatsByName(String name){
        JSONArray ret = new JSONArray();
        List<User> users = userMapper.selectList(new QueryWrapper<User>().like("uName",name));
        List<Counselor> counselors = counselorMapper.selectList(new QueryWrapper<Counselor>().like("cName",name));
        users.forEach(user -> ret.addAll(parseUsersToJSONArray(consultationMapper.selectList(new QueryWrapper<Consultation>().eq("uid",user.getUserId())))));
        counselors.forEach(counselor -> ret.addAll(parseUsersToJSONArray(consultationMapper.selectList(new QueryWrapper<Consultation>().eq("uid",counselor.getCid())))));
        return ret;
    }
    private JSONArray parseUsersToJSONArray(List<Consultation> consultations){
        JSONArray res = new JSONArray();
        consultations.forEach(consultation -> {
            JSONObject msg = new JSONObject();
            try {
                msg.put("time",timeUtil.getTimeDiff(consultation));
            } catch (ParseException e) {
                e.printStackTrace();
            }
            msg.put("id",consultation.getHid());
            msg.put("date",consultation.getBeginTime());
            msg.put("rank",consultation.getCrank());
            //获取咨询者名字
            if (consultation.getUid()<20000){
                msg.put("uName",userMapper.selectById(consultation.getUid()).getUsername());
            }
            else {
                msg.put("uName",counselorMapper.selectById(consultation.getUid()).getName());
            }
            //获取被咨询者名字
            if (consultation.getCid()<30000){
                msg.put("cName",counselorMapper.selectById(consultation.getCid()).getName());
            }
            else {
                msg.put("cName",supervisorMapper.selectById(consultation.getCid()).getName());
            }
            res.add(msg);
        });
        return res;
    }
    /**
     * 机构管理员使用，获取机构管理员信息
     */
    public JSONObject getUserInfo(String phoneNum,String password) throws ParseException {
        JSONObject res = new JSONObject();
        if (!checkPhoneNum(phoneNum)){
            res.put("result","1");
            return res;
        }
        Administrator administrator = administratorMapper.selectOne(new QueryWrapper<Administrator>().eq("aPhoneNum",phoneNum));
        if (!checkPassword(administrator.getAid(),password)){
            res.put("result","2");
            return res;
        }
        res.put("result","3");
        //个人信息
        res.put("id",administrator.getAid());
        res.put("username",administrator.getUsername());
        res.put("name",administrator.getName());
        //今日总咨询数
        SimpleDateFormat formatter= new SimpleDateFormat("yyyy-MM-dd");
        Date today = new Date(System.currentTimeMillis());
        List<Consultation> consultations = consultationMapper.selectList(new QueryWrapper<Consultation>()
                .ge("h_beginTime",formatter.format(today)));
        res.put("sumOfConsToday",consultations.size());
        //今日咨询时长
        res.put("sumOfTimeToday",timeUtil.getTimeDiff(new HashSet<>(consultations)));
        //今日咨询数量变化,每个小时的咨询数
        res.put("sumOfEachHour",getSumOfEachHourToday());
        //所有在线咨询师
        res.put("onlineCounselors",getOnlineCounselors());
        //所有在线督导
        res.put("onlineSupervisors",getOnlineSupervisors());
        //七日咨询量
        res.put("sumOfConsInWeek",getSumOfConsInWeek());
        //最近三十日咨询数排行
        res.put("sumOfConsInMonth",getSumOfConsInMonth());
        //最近一个月好评排行
        res.put("rankOfConsInMonth",getRankOfConsInMonth());
        return res;
    }
    //检查phoneNum是否存在
    private boolean checkPhoneNum(String phoneNum){
        return administratorMapper.selectOne(new QueryWrapper<Administrator>().eq("aPhoneNum",phoneNum)) != null;
    }
    //检查密码是否正确
    private boolean checkPassword(int id,String password){
        return password.equals(loginInfoMapper.selectById(id).getPassword());
    }
    //24小时内，每个小时内的咨询总数
    public int[] getSumOfEachHourToday(){
        int[] sums = new int[24];
        for(int i=0;i<24;i++){
            StringBuilder now = new StringBuilder(timeUtil.getNow());
            StringBuilder next = new StringBuilder(timeUtil.getNow());
            if (i<10){
                now.append(" ").append("0").append(i).append(":00:00");
                next.append(" ").append("0").append(i).append(":59:59");
            }
            else {
                now.append(" ").append(i).append(":00:00");
                next.append(" ").append(i).append(":59:59");
            }
            sums[i] = Math.toIntExact(consultationMapper.selectCount(new QueryWrapper<Consultation>()
                    .le("h_endTime", next.toString())
                    .ge("h_endTime", now.toString())));
        }
        return sums;
    }
    //获取所有在线咨询师
    private JSONArray getOnlineCounselors(){
        JSONArray res = new JSONArray();
        stateMapper.selectList(new QueryWrapper<State>().eq("state",0)
                .lt("id",30000)).forEach(state -> {
                    JSONObject ret = new JSONObject();
                    Counselor counselor = counselorMapper.selectById(state.getId());
                    ret.put("name",counselor.getName());
                    ret.put("isFree",""+timeUtil.isFree(counselor.getCid()));
                    res.add(ret);
        });
        return res;
    }
    //获取所有在线督导
    private JSONArray getOnlineSupervisors(){
        JSONArray res = new JSONArray();
        stateMapper.selectList(new QueryWrapper<State>().eq("state",0)
                .ge("id",30000)).forEach(state -> {
            JSONObject ret = new JSONObject();
            Supervisor supervisor = supervisorMapper.selectById(state.getId());
            ret.put("name",supervisor.getName());
            ret.put("isFree",""+timeUtil.isFree(supervisor.getSid()));
            res.add(ret);
        });
        return res;
    }
    //七日咨询量
    public JSONArray getSumOfConsInWeek(){
        JSONArray res = new JSONArray();
        for (int i=-1;i>=-7;i--){
            Calendar cal=Calendar.getInstance();
            cal.add(Calendar.DATE,i);
            java.util.Date d= cal.getTime();
            SimpleDateFormat sp=new SimpleDateFormat("yyyy-MM-dd");
            StringBuilder date = new StringBuilder(sp.format(d));//获取昨天日期
            JSONObject ret = new JSONObject();
            ret.put("date",date);
            ret.put("sum",consultationMapper.selectCount(new QueryWrapper<Consultation>()
                    .ge("h_endTime", sp.format(d) + " " + "00:00:00")
                    .le("h_endTime", sp.format(d) + " " + "23:59:59")));
            res.add(ret);
        }
        return res;
    }
    //一个月内咨询数排行
    public JSONArray getSumOfConsInMonth(){
        JSONArray res = new JSONArray();
        counselorMapper.selectList(null).forEach(counselor -> {
            JSONObject ret = new JSONObject();
            String[] bounds = timeUtil.getFirstAndLastDayOfMonth();
            ret.put("sum",consultationMapper.selectCount(new QueryWrapper<Consultation>()
                    .eq("cid",counselor.getCid())
                    .ge("h_endTime",bounds[0]+" 00:00:00")
                    .le("h_endTime",bounds[1]+" 23:59:59")));
            ret.put("avatar",counselor.getProfilePhoto());
            ret.put("name",counselor.getName());
            res.add(ret);
        });
        res.sort(Comparator.comparing(obj -> ((JSONObject) obj).getString("sum")).reversed());
        return res;
    }
    //一个月内好评排行
    public JSONArray getRankOfConsInMonth(){
        JSONArray res = new JSONArray();
        counselorMapper.selectList(null).forEach(counselor -> {
            JSONObject ret = new JSONObject();

            String[] bounds = timeUtil.getFirstAndLastDayOfMonth();
            List<Consultation> consultations = consultationMapper.selectList(new QueryWrapper<Consultation>()
                    .eq("cid",counselor.getCid())
                    .ge("h_endTime",bounds[0]+" 00:00:00")
                    .le("h_endTime",bounds[1]+" 23:59:59"));
            ret.put("averageRank",timeUtil.averageRank(consultations));
            ret.put("avatar",counselor.getProfilePhoto());
            ret.put("name",counselor.getName());
            res.add(ret);
        });
        res.sort(Comparator.comparing(obj -> ((JSONObject) obj).getString("averageRank")).reversed());
        return res;
    }
}


